<div style="margin-left:auto; margin-right:auto; width:61%;">
<div style="font-weight:bold; font-size:25px; text-align:center">Customer Balance Detail</div>

<?php
mysql_select_db($database_drycleaners_conn, $drycleaners_conn);
$query_customer = "SELECT customer.id as customer_id, person.first_name, person.last_name FROM customer inner join person on (customer.person_id=person.id) order by person.last_name ASC ";
$customer = mysql_query($query_customer, $drycleaners_conn) or die(mysql_error());
$row_customer = mysql_fetch_assoc($customer);
$totalRows_customer = mysql_num_rows($customer);
?>
<?php 
if(isset($_REQUEST['export'])){
header( "Content-Type: application/vnd.ms-excel" );
	header( "Content-disposition: attachment; filename=Customer_statement".date("d-M-Y",strtotime($_REQUEST['from_date']))."-".date("d-M-Y",strtotime($_REQUEST['to_date'])).".xls" );	
}
?>
<div style="font-weight:bold; font-size:15px; text-align:center">
<?php
echo "BETWEEN ".date("d-M-Y",strtotime($_REQUEST['from_date']))." AND ".date("d-M-Y",strtotime($_REQUEST['to_date']));
?>
</div>
<?php
if(isset($_REQUEST['customers_statement_exporter'])){	
?>
<table  cellpadding="0" cellspacing="0" border="0" id="dataTable">
   <thead>
     <tr>
        <th width="148" >Transaction/Customer</th>
        <th width="165" >Date</th>
        <th width="213">Transaction ID</th>
        <th width="161" >Amount</th>
        
    </tr>
    </thead>
<?php
	do{
		?>
    <tr>
        <th><?php echo $row_customer['first_name']." ".$row_customer['last_name']." Id:".$row_customer['customer_id'] ?></th>
        <th></th>
        <th></th>
        <th></th>
    </tr>
  <?php
		
	mysql_select_db($database_drycleaners_conn, $drycleaners_conn);
$query_order = "SELECT sum(order_payment.amount) as total,
       `order`.id,
       person.first_name,
       person.last_name,
	   order.created_date,
       `order`.order_no
  FROM    (   (   drycleaners.customer customer
               INNER JOIN
                  drycleaners.person person
               ON (customer.person_id = person.id))
           INNER JOIN
              drycleaners.`order` `order`
           ON (`order`.customer_id = customer.id))
       LEFT OUTER JOIN
          drycleaners.order_payment order_payment
       ON (order_payment.order_id = `order`.id)
 WHERE  order.created_date BETWEEN '".$_REQUEST['from_date']." 00:00:00"."' AND '".$_REQUEST['to_date']." 23:00:00"."'  AND order.customer_id='".$row_customer['customer_id']."'
 Group By `order`.id
 ";
$order = mysql_query($query_order, $drycleaners_conn) or die(mysql_error());
$row_order = mysql_fetch_assoc($order);
$totalRows_order = mysql_num_rows($order);



mysql_select_db($database_drycleaners_conn, $drycleaners_conn);
$query_order_payments = "SELECT order_payment.amount,
       `order`.id,
	   order_payment.id as payment_id,
       `order`.created_date, order.order_no,
       order_payment.created_date as payment_date
  FROM    drycleaners.order_payment order_payment
        INNER JOIN
          drycleaners.`order` `order`
       ON (order_payment.order_id = `order`.id)
 WHERE order.customer_id='".$row_customer['customer_id']."' AND order.created_date BETWEEN '".$_REQUEST['from_date']." 00:00:00"."' AND '".$_REQUEST['to_date']." 23:00:00"."' 
 ";
$order_payments = mysql_query($query_order_payments, $drycleaners_conn) or die(mysql_error());
$row_order_payments = mysql_fetch_assoc($order_payments);
$totalRows_order_payments = mysql_num_rows($order_payments);
?>

<?php
$branch_total_bill=0;
if($totalRows_order>0)
do{
?>
<tr>
<td>Order</td>
<td align="left"><?php echo date("d-M-Y H:i:s",strtotime($row_order["created_date"]))?></td>
<td align="left"><?php echo $row_order["order_no"]?></td>
<?php $total_bill=0;?>

<?php
mysql_select_db($database_drycleaners_conn, $drycleaners_conn);
$query_order_item_by_order_id = sprintf("SELECT material.name as material_name,
       item.name as item_name,
       type_of_service.name as type_of_service_name,
       order_item.color,
	   order_item.id,
	   order_item.material_id,
	   order_item.item_id
  FROM    (   (   drycleaners.order_item order_item
               INNER JOIN
                  drycleaners.type_of_service type_of_service
               ON (order_item.type_of_service_id = type_of_service.id))
           INNER JOIN
              drycleaners.item item
           ON (order_item.item_id = item.id))
       INNER JOIN
          drycleaners.material material
       ON (order_item.material_id = material.id)
	   WHERE
	    order_id =%s", GetSQLValueString($row_order['id'], "int"));
$order_item_by_order_id = mysql_query($query_order_item_by_order_id, $drycleaners_conn) or die(mysql_error());
$row_order_item_by_order_id = mysql_fetch_assoc($order_item_by_order_id);

do{
	$cost=0;
	
	mysql_select_db($database_drycleaners_conn, $drycleaners_conn);
$query_order_item_service = "SELECT order_item_service.id, service.name as service_name,order_item_service.service_id
  FROM    drycleaners.order_item_service order_item_service
       INNER JOIN
          drycleaners.service service
       ON (order_item_service.service_id = service.id) WHERE order_item_id='".$row_order_item_by_order_id['id']."'";
	   
$order_item_service = mysql_query($query_order_item_service, $drycleaners_conn) or die(mysql_error());
$row_order_item_service = mysql_fetch_assoc($order_item_service);
	

	do{
		
		$query_prices="SELECT material_service_cost.price, material_service_cost.express_extra_price
  FROM drycleaners.material_service_cost material_service_cost Where material_id='".$row_order_item_by_order_id['material_id']."' AND item_id='".$row_order_item_by_order_id['item_id']."' AND service_id='".$row_order_item_service['service_id']."'";

  $get_prices=mysql_query($query_prices,$drycleaners_conn) or die(mysql_error());
  $row_get_prices = mysql_fetch_assoc($get_prices);
	   
	   if($row_order_item_by_order_id["name"]='express'){
	   $service_cost=$row_get_prices['price']+$row_get_prices['express_extra_price'];
	   }
	   else{
		   $service_cost=$row_get_prices['price'];
		   }
		   
		   $cost=$cost+$service_cost;
		}while($row_order_item_service=mysql_fetch_assoc($order_item_service));
	$total_bill=$total_bill+$cost;
	}while($row_order_item_by_order_id=mysql_fetch_assoc($order_item_by_order_id));
?>
            <td align="center"><?php echo $total_bill;?></td>
      </td>
            </tr>
<?php
mysql_free_result($order_item_by_order_id);

mysql_free_result($order_item_service);
?>
<?php
$branch_total_bill=$branch_total_bill+$total_bill;
 }while($row_order=mysql_fetch_assoc($order));
 $total_payment=0;
?>

    <?php if($totalRows_order_payments>0){
		
		do{
	?>
    <tr>
    <td>Payment</td>
    <td align="left"><?php if($row_order_payments["payment_date"]!=''&& $row_order_payments["payment_date"]!='0000-00-00 00:00:00' ){echo date("d-M-Y H:i:s",strtotime($row_order_payments["payment_date"]));} else{echo date("d-M-Y H:i:s",strtotime($row_order_payments["created_date"])); }?></td>
    
    <td align="left"><?php if($row_order_payments["payment_date"]!=''&& $row_order_payments["payment_date"]!='0000-00-00 00:00:00' ){echo "PMT#".$row_order_payments["payment_id"]." on ".$row_order_payments["order_no"];} else{echo $row_order_payments["order_no"]." Due Date".$row_order_payments["created_date"]; }?></td>
    
    <td align="center"><?php
				if($row_order_payments['amount']>0){$minus="-";}
	 echo "-".$row_order_payments['amount'];?></td>
</tr>
<?php 
$total_payment=$total_payment+$row_order_payments['amount'];
} while($row_order_payments=mysql_fetch_assoc($order_payments));
	}
?>
     <tr>
        <th align="left" bordercolor="#000000" bordercolordark="#000000"></th>
        <th></th>
        <th>
        </th>
        <th>
        <?php if(!isset($_REQUEST['export'])){echo "<hr style='margin-top:10px; margin-bottom:0px;'/>";} ?>
		<?php echo $branch_total_bill-$total_payment;?>
        <?php if(!isset($_REQUEST['export'])){echo "<hr style='margin-top:-0px;'/>";} ?>
        </th>
    </tr>

<?php
}while($row_customer=mysql_fetch_assoc($customer));
}

?>
</table>
    <?php
mysql_free_result($customer);
?>
</div>